package com.crcode.crm.filter.impl;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.UUID;

import org.apache.solr.client.solrj.impl.HttpSolrServer;
import org.apache.solr.common.SolrInputDocument;

import com.crcode.crm.utils.DBUtil;
import com.crcode.crm.utils.PropertiesUtil;
import com.crcode.crm.utils.SolrIndexUtil;
/**
 * @author xzq
 * @date 创建时间：2018年4月3日 上午11:09:21
 * @version 1.0
 */
public class IndexImpl {
	/**
	 * 创建索引
	 * 
	 * @author xzq
	 * @throws IOException
	 */
	public static void createIndex() throws Exception {
		try {
			PropertiesUtil p = new PropertiesUtil();
			// 获取properties配置文件数据
			String solrurl = p.getTerm("solr.properties", "solrcore");
			HttpSolrServer solr = new HttpSolrServer(solrurl);
			Collection<SolrInputDocument> docs = new ArrayList<SolrInputDocument>();// 对象list
			String sql = "delete from arc_index_vindicate where table_name='data_enterprise' and "
					+ "unique_id not in(select id from data_enterprise) and index_type=2";
			// 删除多余数据
			Connection conn2 = DBUtil.getConnection();
			PreparedStatement pstmt = (PreparedStatement) conn2.prepareStatement(sql);
			int m = pstmt.executeUpdate();
			sql = "delete from arc_index_vindicate where table_name='data_project' and "
					+ "unique_id not in(select id from data_project) and index_type=2";
			pstmt = (PreparedStatement) conn2.prepareStatement(sql);
			m += pstmt.executeUpdate();
			System.out.println("删除失效数据条数:" + m);
			conn2.close();
			pstmt.close();
			sql = "select distinct table_name from arc_index_vindicate where index_type=2";
			Connection conn = DBUtil.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			List<String> tables = new ArrayList<>();
			while (rs.next()) {
				tables.add(rs.getString("table_name"));
			}
			System.out.println(tables);
			DBUtil.close(rs, stmt, conn);
			for (String table : tables) {
				delteMoreData(table);
				// 获取总数
				sql = "select count(1) c from arc_index_vindicate where table_name='" + table + "' and index_type=2";
				conn = DBUtil.getConnection();
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				int total = 0;
				int count = 0;
				while (rs.next()) {
					count = rs.getInt("c");
				}
				DBUtil.close(rs, stmt, conn);
				System.out.println("总条数" + total);
				// 每次循环一百条数据
				if (count < 1000) {
					total = 1;
				} else {
					total = count / 1000;
					if (count % 1000 > 0) {
						total++;
					}
				}
				for (int i = 0; i < total; i++) {
//					sql = "select * from (select id onlyId,unique_id from arc_index_vindicate"
//							+ " where table_name='" + table + "' and index_type=2 limit 0,10 ) a" + ""
//									+ " inner join (select * from "
//							+ table + " )b" + " on a.unique_id=b.id";
					sql="select * from "+table+" where id in(select unique_id from arc_index_vindicate"
							+ " where table_name='"+table+"' and index_type=2 ) limit 1000";					
					conn = DBUtil.getConnection();
					stmt = conn.createStatement();
					rs = stmt.executeQuery(sql);
					ResultSetMetaData data = rs.getMetaData();
					List<Map<String, Object>> rows = new ArrayList<>();
					String ids = "";
					while (rs.next()) {
						ids += rs.getString("id") + ",";
						Map<String, Object> map = new HashMap<>();
						for (int k = 1; k <= data.getColumnCount(); k++) {
							String columnName = data.getColumnName(k);
							String value = rs.getString(columnName) == null ? "" : rs.getString(columnName);
							if("id".equals(columnName)){
								map.put(columnName, Integer.parseInt(value));
							}else{								
								map.put(columnName, value);
							}
						}
						map.put("onlyId", UUID.randomUUID());
						map.put("tableName", table);
						rows.add(map);
					}
					DBUtil.close(rs, stmt, conn);
					for (Map<String, Object> row : rows) {
						SolrInputDocument document = new SolrInputDocument();
						for (Entry<String, Object> en : row.entrySet()) {
							document.setField(en.getKey(), en.getValue());
						}
						docs.add(document);
					}
					if (docs.size() > 0) {
						solr.add(docs);
						solr.commit();
						docs = new ArrayList<SolrInputDocument>();
						// 删除索引表数据
						sql = "delete from arc_index_vindicate  where table_name='" + table + "'"
								+ " and index_type=2 and unique_id in(" + ids.substring(0, ids.length() - 1) + ")";
						conn2 = DBUtil.getConnection();
						pstmt = (PreparedStatement) conn2.prepareStatement(sql);
						m = pstmt.executeUpdate();
						System.out.println("删除数据条数:" + m);
						conn2.close();
						pstmt.close();
					}

				}
				// 删除失效数据
				sql = "delete from arc_index_vindicate  where table_name='" + table + "'"
						+ " and unique_id not in(select id from " + table + ") and index_type=2";
				conn2 = DBUtil.getConnection();
				pstmt = (PreparedStatement) conn2.prepareStatement(sql);
				m = pstmt.executeUpdate();
				System.out.println("删除数据条数:" + m);
				conn2.close();
				pstmt.close();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 修改索引
	public static void updateAndDeleteIndex() {
		try {
			// 先编辑后执行删除
			PropertiesUtil p = new PropertiesUtil();
			// 获取properties配置文件数据
			String solrurl = p.getTerm("solr.properties", "solrcore");
			HttpSolrServer solr = new HttpSolrServer(solrurl);
			String sql = "select distinct table_name from arc_index_vindicate where index_type<>2";
			Connection conn = DBUtil.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			List<String> tables = new ArrayList<>();
			while (rs.next()) {
				tables.add(rs.getString("table_name"));
			}
			DBUtil.close(rs, stmt, conn);
			for (String table : tables) {
				// 先获取被编辑的总数
				sql = "select count(1) c from arc_index_vindicate where table_name='" + table + "' and index_type<>2";
				conn = DBUtil.getConnection();
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				int total = 0;
				int count = 0;
				while (rs.next()) {
					count = rs.getInt("c");
				}
				if (count > 0) {
					total = count / 100;
					if (count % 100 > 0) {
						total++;
					}
				}
				DBUtil.close(rs, stmt, conn);
				System.out.println("总条数" + total);
				// 每次循环一百条数据
				for (int i = 0; i < total; i++) {
					sql = "select id onlyId,unique_id,index_type from arc_index_vindicate where table_name='"
							+ table + "' and index_type<>2 limit 0,100 ";
					conn = DBUtil.getConnection();
					stmt = conn.createStatement();
					rs = stmt.executeQuery(sql);
					String ids = "";
					String updateIds = "";
					while (rs.next()) {
						String onlyId = rs.getString("onlyId");
						Integer indexType = rs.getInt("index_type");
						// 先删除
						int uniqueValue = rs.getInt("unique_id");
						String query = "(tableName:" + table + " AND id:" + uniqueValue + ")";
						solr.deleteByQuery(query);
						solr.commit();
						if (indexType == 0) {
							ids += onlyId + ",";
						} else {
							updateIds += onlyId + ",";

						}
					}
					DBUtil.close(rs, stmt, conn);
					if (ids.length() > 0) {
						sql = "delete from arc_index_vindicate  where table_name='" + table + "'" + "  and id in("
								+ ids.substring(0, ids.length() - 1) + ")";
						Connection conn2 = DBUtil.getConnection();
						PreparedStatement pstmt = (PreparedStatement) conn2.prepareStatement(sql);
						int m = pstmt.executeUpdate();
						System.out.println("删除数据条数:" + m);
						conn2.close();
						pstmt.close();
					}
					if (updateIds.length() > 0) {
						sql = "update arc_index_vindicate set index_type=2 where id in("
								+ updateIds.substring(0, updateIds.length() - 1) + ")";
						Connection conn2 = DBUtil.getConnection();
						PreparedStatement pstmt = (PreparedStatement) conn2.prepareStatement(sql);
						pstmt.executeUpdate();
						conn2.close();
						pstmt.close();
					}
				}
			}
			 createIndex();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 删除多余数据
	 * @param tableName
	 * @throws IOException
	 * @throws SQLException
	 */
	public static void delteMoreData(String tableName) throws IOException, SQLException{
		String sql="delete from arc_index_vindicate where table_name='"+tableName+"'"
				+ " and unique_id in (select unique_id from (select unique_id,count(1)"
				+ " c,max(id) mid from (select * from arc_index_vindicate where table_name='"+tableName+"'"
				+ " and index_type=2)a group by unique_id having count(1)>1 )b) and id not"
				+ " in(select mid from (select unique_id,count(1) c,max(id) mid from (select *"
				+ " from arc_index_vindicate where table_name='"+tableName+"' and index_type=2)a "
				+ "group by unique_id having count(1)>1 )b)";
		Connection conn2 = DBUtil.getConnection();
		PreparedStatement pstmt = (PreparedStatement) conn2.prepareStatement(sql);
		pstmt.executeUpdate();
		conn2.close();
		pstmt.close();
	}
	// 文件索引修改
	public static void updateAndDeleteFileIndex() throws IOException, SQLException {

		Connection conn = DBUtil.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "  delete from arc_index_vindicate where " + "table_name='d_assr' and index_type<>0"
					+ " and unique_id not in(select id from d_assr where data_type=1) ";
			Connection conn2 = DBUtil.getConnection();
			PreparedStatement pstmt = (PreparedStatement) conn2.prepareStatement(sql);
			int m = pstmt.executeUpdate();
			System.out.println("删除数据条数:" + m);
			conn2.close();
			pstmt.close();
			// 修改索引
			sql = "select * from (select * from (select unique_id,id uid from arc_index_vindicate"
					+ " where table_name='d_assr' and index_type=1)a inner join"
					+ " (select id rid ,name fname,path,c_id,xmfwbm from d_assr)b on a.unique_id=b.rid )"
					+ " f inner join (select * from (select id cid,name,z_id from d_assc )"
					+ " e inner join (select xmmc,id ssid from d_aasz)c on e.z_id=c.ssid)d" + " on f.c_id=d.cid";
			rs = stmt.executeQuery(sql);
			List<Map<String, String>> rows = new ArrayList<>();
			while (rs.next()) {
				Map<String, String> row = new HashMap<>();
				row.put("unique_id", rs.getString("unique_id"));
				row.put("uid", rs.getString("uid"));
				row.put("fname", rs.getString("fname"));
				row.put("path", rs.getString("path"));
				row.put("xmmc", rs.getString("xmmc"));
				row.put("cxmmc", rs.getString("name"));// 子表项目名称
				row.put("zid", rs.getString("z_id"));
				row.put("cid", rs.getString("cid"));
				row.put("xmfwbm", rs.getString("xmfwbm"));
				rows.add(row);
			}
			DBUtil.close(rs, stmt, conn);
			String xlurl = new PropertiesUtil().getTerm("config.properties", "contextRoot").substring(5);
			SolrIndexUtil siu = new SolrIndexUtil();
			for (Map<String, String> row : rows) {
				Object unique_id = row.get("unique_id");
				Object id = row.get("uid");
				// 先删除
				String query = "archiveid:" + unique_id + " AND tablename:d_assr";
				siu.delPDFIndexs(query);
				// 后添加
				siu.indexFilesSolrCell(xlurl + "/" + row.get("path"), row.get("uid"), row.get("unique_id"), "d_assr",
						row.get("xmmc"), row.get("cxmmc"), row.get("cid"), row.get("zid"), row.get("fname"),
						row.get("path"), row.get("xmfwbm"));
				sql = "delete from arc_index_vindicate where id=" + id;
				conn2 = DBUtil.getConnection();
				pstmt = (PreparedStatement) conn2.prepareStatement(sql);
				m = pstmt.executeUpdate();
				System.out.println("删除数据条数:" + m);
				conn2.close();
				pstmt.close();
			}
			// 删除索引
			sql = "select unique_id,id from arc_index_vindicate where table_name='d_assr' and index_type=0";
			conn = DBUtil.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			rows = new ArrayList<>();
			while (rs.next()) {
				Map<String, String> row = new HashMap<>();
				row.put("unique_id", rs.getString("unique_id"));
				row.put("id", rs.getString("id"));
				rows.add(row);
			}
			for (Map<String, String> row : rows) {
				Object unique_id = row.get("unique_id");
				Object id = row.get("id");
				// 删除
				String query = "archiveid:" + unique_id + " AND tablename:d_assr";
				siu.delPDFIndexs(query);
				sql = "delete from arc_index_vindicate where id=" + id;
				conn2 = DBUtil.getConnection();
				pstmt = (PreparedStatement) conn2.prepareStatement(sql);
				m = pstmt.executeUpdate();
				System.out.println("删除数据条数:" + m);
				conn2.close();
				pstmt.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
			DBUtil.close(rs, stmt, conn);
		}

	}

	/**
	 * 新增全文索引
	 * 
	 * @throws Exception
	 */
	public static void createFileIndex() throws Exception {
		Connection conn = DBUtil.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "  delete from arc_index_vindicate where " + "table_name='d_assr' and index_type<>0"
					+ " and unique_id not in(select id from d_assr where data_type=1) ";
			Connection conn2 = DBUtil.getConnection();
			PreparedStatement pstmt = (PreparedStatement) conn2.prepareStatement(sql);
			int m = pstmt.executeUpdate();
			System.out.println("删除数据条数:" + m);
			conn2.close();
			pstmt.close();
			// 添加索引
			sql = "select * from (select * from (select unique_id,id uid from arc_index_vindicate"
					+ " where table_name='d_assr' and index_type=2)a inner join"
					+ " (select id rid ,name fname,path,c_id,xmfwbm from d_assr)b on a.unique_id=b.rid )"
					+ " f inner join (select * from (select id cid,name,z_id from d_assc )"
					+ " e inner join (select xmmc,id ssid from d_aasz)c on e.z_id=c.ssid)d" + " on f.c_id=d.cid";
			rs = stmt.executeQuery(sql);
			List<Map<String, String>> rows = new ArrayList<>();
			while (rs.next()) {
				Map<String, String> row = new HashMap<>();
				row.put("unique_id", rs.getString("unique_id"));
				row.put("uid", rs.getString("uid"));
				row.put("fname", rs.getString("fname"));
				row.put("path", rs.getString("path"));
				row.put("xmmc", rs.getString("xmmc"));
				row.put("cxmmc", rs.getString("name"));// 子表项目名称
				row.put("zid", rs.getString("z_id"));
				row.put("cid", rs.getString("cid"));
				row.put("xmfwbm", rs.getString("xmfwbm"));
				rows.add(row);
			}
			DBUtil.close(rs, stmt, conn);
			String xlurl = new PropertiesUtil().getTerm("config.properties", "contextRoot").substring(5);
			SolrIndexUtil siu = new SolrIndexUtil();
			for (Map<String, String> row : rows) {
				siu.indexFilesSolrCell(xlurl + "/" + row.get("path"), row.get("uid"), row.get("unique_id"), "d_assr",
						row.get("xmmc"), row.get("cxmmc"), row.get("cid"), row.get("zid"), row.get("fname"),
						row.get("path"), row.get("xmfwbm"));
				sql = "delete from arc_index_vindicate where id=" + row.get("uid");
				conn2 = DBUtil.getConnection();
				pstmt = (PreparedStatement) conn2.prepareStatement(sql);
				m = pstmt.executeUpdate();
				System.out.println("删除数据条数:" + m);
				conn2.close();
				pstmt.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
			DBUtil.close(rs, stmt, conn);
		}
	}

	/**
	 * 索引测试
	 */
	public static void testSolr() {
		try {
			// 先编辑后执行删除
			PropertiesUtil p = new PropertiesUtil();
			// 获取properties配置文件数据
			String solrurl = p.getTerm("solr.properties", "solrcore");
			HttpSolrServer solr = new HttpSolrServer(solrurl);
			 String query = "(onlyId:**)";
			 solr.deleteByQuery(query);
			 solr.commit();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static void main(String[] args) throws Exception {
		 createIndex();
//		testSolr();
	//	updateAndDeleteIndex();
		
//		String str="fdsaljfld;jkdsgfkhlsdfakhe;rfkjhhfkjhfhjhkjhfsk";
//		System.out.println(str.substring(str.indexOf(";")+1));
	}
}
